package com.apes.fn.tms.repository;

import com.apes.fn.tms.model.Waybill;
import com.apes.fn.tms.model.WaybillRoutePlanning;
import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface WaybillRoutePlanningRepository extends CustomRepository<WaybillRoutePlanning, String> {
    @Modifying
    @Query(value = " MERGE INTO TMS_WAYBILL_ROUTE_PLANNING A " +
            " USING (SELECT A.ID, A.DURATION_TOTAL, A.DEPARTURE_STATION, " +
            "               DECODE(A.LINE_TYPE, 'MainLine', A.TERMINAL, 'BranchLine', B.PHY_DEPOT_ID) TERMINAL, " +
            "               CASE WHEN (SELECT CUTOFF_TIME " +
            "                         FROM (SELECT J.SITE_ID, TO_CHAR(CUTOFF_TIME, 'HH24:MI') CUTOFF_TIME, " +
            "                                       ROW_NUMBER() OVER(PARTITION BY SITE_ID ORDER BY TO_CHAR(CUTOFF_TIME, 'HH24:MI')) XH " +
            "                                  FROM TMS_BRANCH_SHIFT J, TMS_BRANCH_SHIFT_ITEM K " +
            "                                 WHERE J.ID = K.BRANCH_SHIFT_ID " +
            "                                   AND TO_CHAR(SYSDATE, 'HH24:MI') < TO_CHAR(CUTOFF_TIME, 'HH24:MI')) " +
            "                        WHERE SITE_ID = A.DEPARTURE_STATION " +
            "                          AND XH = 1) IS NOT NULL THEN " +
            "                  TO_CHAR(SYSDATE, 'YYYY-MM-DD ') || " +
            "                  (SELECT CUTOFF_TIME " +
            "                     FROM (SELECT J.SITE_ID, TO_CHAR(CUTOFF_TIME, 'HH24:MI') CUTOFF_TIME, " +
            "                                   ROW_NUMBER() OVER(PARTITION BY SITE_ID ORDER BY TO_CHAR(CUTOFF_TIME, 'HH24:MI')) XH " +
            "                              FROM TMS_BRANCH_SHIFT J, TMS_BRANCH_SHIFT_ITEM K " +
            "                             WHERE J.ID = K.BRANCH_SHIFT_ID " +
            "                               AND TO_CHAR(SYSDATE, 'HH24:MI') < TO_CHAR(CUTOFF_TIME, 'HH24:MI')) " +
            "                    WHERE SITE_ID = A.DEPARTURE_STATION AND XH = 1) " +
            "                 WHEN (SELECT CUTOFF_TIME " +
            "                         FROM (SELECT J.SITE_ID, TO_CHAR(CUTOFF_TIME, 'HH24:MI') CUTOFF_TIME, " +
            "                                       ROW_NUMBER() OVER(PARTITION BY SITE_ID ORDER BY TO_CHAR(CUTOFF_TIME, 'HH24:MI')) XH " +
            "                                  FROM TMS_BRANCH_SHIFT J, TMS_BRANCH_SHIFT_ITEM K " +
            "                                 WHERE J.ID = K.BRANCH_SHIFT_ID " +
            "                                   AND TO_CHAR(SYSDATE, 'HH24:MI') > TO_CHAR(CUTOFF_TIME, 'HH24:MI')) " +
            "                        WHERE SITE_ID = A.DEPARTURE_STATION AND XH = 1) IS NOT NULL THEN " +
            "                  TO_CHAR(SYSDATE + 1, 'YYYY-MM-DD ') || " +
            "                  (SELECT CUTOFF_TIME " +
            "                     FROM (SELECT J.SITE_ID, TO_CHAR(CUTOFF_TIME, 'HH24:MI') CUTOFF_TIME, " +
            "                                   ROW_NUMBER() OVER(PARTITION BY SITE_ID ORDER BY TO_CHAR(CUTOFF_TIME, 'HH24:MI')) XH " +
            "                              FROM TMS_BRANCH_SHIFT J, TMS_BRANCH_SHIFT_ITEM K " +
            "                             WHERE J.ID = K.BRANCH_SHIFT_ID " +
            "                               AND TO_CHAR(SYSDATE, 'HH24:MI') > TO_CHAR(CUTOFF_TIME, 'HH24:MI')) " +
            "                    WHERE SITE_ID = A.DEPARTURE_STATION AND XH = 1) " +
            "                 ELSE NULL END CUTOFF_TIME, " +
            "               TO_NUMBER(DECODE(A.LINE_TYPE, 'Mainline', A.DEPARTURE_STATION, 'BranchLine', B.TIME_TOTAL)) TIME_TOTAL " +
            "          FROM TMS_BRANCH_LINE A, TMS_BRANCH_LINE_ITEM B " +
            "         WHERE A.ID = B.BRANCH_LINE_ID) B " +
            " ON (A.LINE_ID = B.ID AND A.DELIVERY_ADDRESS = B.DEPARTURE_STATION AND A.SHIPPING_ADDRESS = B.TERMINAL) " +
            " WHEN MATCHED THEN " +
            "   UPDATE SET A.ESTIMATED_DELIVERY_TIME = TO_DATE(B.CUTOFF_TIME, 'YYYY-MM-DD HH24:MI:SS') + B.TIME_TOTAL / 24 / 60 " +
            "    WHERE A.VALID IN (0, 1) ", nativeQuery = true)
    void updateWaybillRoutePlanning();


    // 查询 方案列表中 同一方案号下最大的执行顺序号的
    @Query(value = "select t.*" +
            "   from tms_waybill_route_planning t" +
            "  where t.execution_number IN" +
            "        (select max(b.execution_number)" +
            "           from tms_waybill_route_planning b" +
            "          WHERE b.PLAN_NUMBER = t.PLAN_NUMBER" +
            "            and b.waybill_id = :waybillId" +
            "          group by b.plan_number)" +
            "    and t.waybill_id = :waybillId",nativeQuery = true)
    List<WaybillRoutePlanning> findByWaybillId(@Param("waybillId") String waybillId);

    // 查询 方案列表中 同一方案号下最大的执行顺序号的
    @Query(value = "select t.*" +
            "   from tms_waybill_route_planning t" +
            "  where t.execution_number IN" +
            "        (select max(b.execution_number)" +
            "           from tms_waybill_route_planning b" +
            "          WHERE b.PLAN_NUMBER = t.PLAN_NUMBER" +
            "            and b.waybill_id = :waybillId" +
            "          group by b.plan_number)" +
            "    and t.waybill_id = :waybillId" +
            "    and t.line_id = :lineId",nativeQuery = true)
    List<WaybillRoutePlanning> findByWaybillIdAndLineId(@Param("waybillId") String waybillId, @Param("lineId") String lineId);

    List<WaybillRoutePlanning> findByWaybillIdAndPlanNumber(Waybill waybillId, int planNumber);
    WaybillRoutePlanning findByWaybillIdAndPlanNumberAndExecutionNumber(Waybill waybillId, int planNumber , int executionNumber);
}
